package org.apogee.mil.server.database;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import org.apache.log4j.Logger;
import org.apogee.mil.server.database.helper.DatabaseConnector;
import org.apogee.mil.server.database.helper.DatabaseHelper;
import org.apogee.mil.server.database.helper.DatabaseQueries;
import org.apogee.mil.shared.Constants.AllCdcFields;
import org.apogee.mil.shared.Constants.GroupFields;
import org.apogee.mil.shared.Constants.GroupTestLookupFields;
import org.apogee.mil.shared.Constants.GroupUserLookupFields;
import org.apogee.mil.shared.Constants.QuestionFields;
import org.apogee.mil.shared.Constants.TestFields;
import org.apogee.mil.shared.Constants.UserLookupFields;
import org.apogee.mil.shared.Constants.UsersFields;
import org.apogee.mil.shared.Question;
import org.apogee.mil.shared.User;

public class DatabaseDriver {

	private static Logger logger = Logger.getLogger(DatabaseDriver.class);

	public enum TableStatus {
		ADDED,
		NOT_ADDED,
		DOES_NOT_EXIST,
		EXIST,
		ERROR
	}

	private Connection con = null;
	private String databaseInfo;
	private boolean initialized = false;
	private int retryCount = 0;

	private DatabaseConnector dbConn;

	public DatabaseDriver() { 
		try {
			dbConn = new DatabaseConnector();
		} catch (Exception e) {
			logger.error(e);
		}
	}

	public boolean initialize() {
		if (initialized) return true;

		try {

			Class.forName(dbConn.getDriver()).newInstance();
			con = DriverManager.getConnection(dbConn.getURL(), dbConn.getUser(), dbConn.getPassword());

			DatabaseMetaData dbmd = con.getMetaData();

			databaseInfo = dbmd.getDatabaseProductName() + " " + dbmd.getDatabaseProductVersion();

			Statement select = con.createStatement();

			ResultSet set = select.executeQuery("select * from users");
			if (set.next())
			{
				initialized = true;
				return true;
			}
		} catch (Exception e) { 
			logger.error(e);
		}
		return false;
	}

	public ResultSet executeQuery(String queryString) {
		ResultSet set = null;
		try {
			logger.info("Executing Query: " + queryString);
			Statement select = con.createStatement();
			set = select.executeQuery(queryString);
		} catch (Exception e) { 
			logger.error("Unable to execute query: " + e.getMessage());
		}

		return set;
	}

	public boolean executeUpdate(String updateString) {
		try {
			logger.info("Executing Update: " + updateString);
			Statement update = con.createStatement();
			int retval = update.executeUpdate(updateString);

			if (retval == 0) 
				return false;

		} catch(Exception e) {
			logger.error("Unable to execute update: " + e.getMessage());
			return false;
		}
		return true;
	}

	public int addQuestion(String test, Question question) {
		int retVal = -1;


		try {
			Integer.parseInt(test.substring(0,1));
			test = "A"+test;
		} catch (NumberFormatException ex) { }

		String enabled = "Y";
		if (question.getEnabled() == false)
			enabled = "N";

		if (question.getId() == -1) {

			retVal = addQuestion(test, question.getQuestion(), question.getChoice(0), question.getChoice(1), question.getChoice(2), question.getChoice(3),
					question.getAnswer(), question.getVolume(), question.getType(), enabled);
		} else {
			String[] fields = QuestionFields.QUESTIONS_FIELDS_2;
			String[] values = {question.getQuestion(), question.getChoice(0), question.getChoice(1), question.getChoice(2), question.getChoice(3),
					String.valueOf(question.getAnswer()), String.valueOf(question.getVolume()), question.getType(), enabled, "sysdate"};

			String sql = DatabaseHelper.createUpdateStatement(test, fields, values, "where id="+question.getId());

			boolean good = executeUpdate(sql);

			if (good) {
				retVal = 1;
			}
		}

		return retVal;

	}
	public int addQuestion(String table, String question, String choice1, String choice2, String choice3, String choice4, int answer, int volume, String review, String enabled) {
		try {
			Integer.parseInt(table.substring(0,1));
			table = "A"+table;
		} catch (NumberFormatException ex) { }

		String[] fields = QuestionFields.QUESTIONS_FIELDS;
		String[] values = {table+"_seq.nextval",question, choice1, choice2, choice3, choice4, String.valueOf(answer),
				String.valueOf(volume), review, enabled, "sysdate", "sysdate" };


		String stmt = DatabaseHelper.createInsertStatement(table, fields, values);


		return executeUpdate(stmt) == false ? -1 : 1;
	}

	public int addQuestion(String table, String question, String choice1, String choice2, String choice3, String choice4, int answer, int volume, String review) {

		return addQuestion(table, question, choice1, choice2, choice3, choice4, answer, volume, review, "Y");
	}

	public TableStatus createDatabase(String dbName) {

		String test = dbName;
		String minScore = "67";

		String[] split = dbName.split(":");

		if (split.length == 2) {
			test = split[0].trim();
			minScore = split[1].trim();
		}

		try {
			Integer.parseInt(test.substring(0,1));
			test = "A"+test;
		} catch (NumberFormatException ex) { }

		String table = "select table_name from user_tables where table_name = '" + test + "'";

		Statement stmt;
		try {
			stmt = con.createStatement();
			ResultSet set = stmt.executeQuery(table);

			if (set.next()) {
				return TableStatus.EXIST;
			}

			String sql = String.format(DatabaseQueries.CREATE_TABLE_QRY, test.toUpperCase(), QuestionFields.ID, QuestionFields.QUESTION,
					QuestionFields.CHOICE_1, QuestionFields.CHOICE_2, QuestionFields.CHOICE_3,
					QuestionFields.CHOICE_4, QuestionFields.ANSWER, QuestionFields.VOLUME,
					QuestionFields.REVIEW_OR_TEST, QuestionFields.ENABLED, QuestionFields.DATE_ADDED,
					QuestionFields.DATE_MODIFIED, QuestionFields.ID);

			executeUpdate(sql);

			sql = String.format("create sequence %s_seq start with 1 increment by 1 nomaxvalue", test);

			executeUpdate(sql);

			String[] fields = AllCdcFields.ALL_CDCS_FIELDS;
			String[] values = {"", test, minScore, "sysdate"};

			sql = DatabaseHelper.createInsertStatement("all_cdcs", fields, values);

			stmt = con.createStatement();

			boolean good =	executeUpdate(sql);
			if (good == false) {
				return TableStatus.ERROR;
			}

		} catch (Exception e) {
			return TableStatus.ERROR;
		}

		return TableStatus.ADDED;
	}

	public User login(String username, String password) {
		User login = null;
		try {

			String stm = String.format(DatabaseQueries.LOGIN_QRY, 
					UsersFields.USER_NAME, username, UsersFields.PASSWORD, password, UsersFields.ENABLED, "Y");

			ResultSet set = executeQuery(stm);

			if (set.next()) {
				stm = String.format(DatabaseQueries.UPDATE_LOGIN_QRY, UsersFields.LAST_LOGIN, UsersFields.USER_ID, set.getInt(1));

				int id = set.getInt(1);
				String user = set.getString(2);
				int access = set.getInt(4);

				login = new User(id, user, access);
				executeUpdate(stm);

				logger.info("Successful update");
				logger.info("User Id: " + id + " User: " + user + " Access: " + access);
			}

		} catch (Exception e) { 
			logger.error("Unable to login with username: " + username);

			if (++retryCount < 5) {
				logger.error("Using failover connection: retry " + retryCount);
				initialized = false;
				initialize();
				login =  login(username, password);

			} else {
				retryCount = 0;
				return null;
			}
		}

		return login;
	}

	public int addUser(int userid, String username, String password, String firstName, String lastName, int accessLevel, String enabled) {

		int retVal = -1;
		String[] fields = UsersFields.USERS_FIELDS;
		String[] values = new String[] { String.valueOf(userid), username, password, String.valueOf(accessLevel),
				enabled, "sysdate", "sysdate" };
		String stmt = DatabaseHelper.createInsertStatement("users", fields, values);


		fields = UserLookupFields.USER_LOOKUP_FIELDS;
		values = new String[] {String.valueOf(userid), firstName, lastName};

		String stmt2 = DatabaseHelper.createInsertStatement("user_lookup", fields, values);

		retVal = executeUpdate(stmt) == true ? 1 : -1;
		retVal += executeUpdate(stmt2) == true ? 1 : -1;

		return retVal;
	}


	public int updateUser(int userid, String username, String password, String firstName, String lastName, int accessLevel, String enabled) {

		int retVal = -1;
		String[] fields = UsersFields.USERS_FIELDS;
		String[] values = new String[] { String.valueOf(userid), username, password, String.valueOf(accessLevel),
				enabled};
		String stmt = DatabaseHelper.createUpdateStatement("users", fields, values, String.format("where %s=%d", UsersFields.USER_ID, userid));


		fields = UserLookupFields.USER_LOOKUP_FIELDS;
		values = new String[] {String.valueOf(userid), firstName, lastName};

		String stmt2 = DatabaseHelper.createUpdateStatement("user_lookup", fields, values, String.format("where %s=%d",UserLookupFields.USER_ID, userid));

		retVal = executeUpdate(stmt) == true ? 1 : -1;
		retVal += executeUpdate(stmt2) == true ? 1 : -1;

		return retVal;
	}

	public int insertGroup(String groupId, String groupName){
		String[] fields = GroupFields.GROUP_FIELDS;
		String[] values = new String[] {groupId,  groupName};

		String stmt = DatabaseHelper.createInsertStatement("groups", fields, values);

		return executeUpdate(stmt) == false ? -1 : 1;
	}

	public int updateGroup(String groupId, String groupName) {

		String[] fields = GroupFields.GROUP_FIELDS;
		String[] values = new String[] {groupId,  groupName};

		String stmt = DatabaseHelper.createUpdateStatement("groups", fields, values, String.format("where %s=%s", GroupFields.GROUP_ID, groupId));

		return executeUpdate(stmt) == false ? -1 : 1;
	}


	public boolean close() {

		logger.info("Closing Connection");
		try {
			con.close();
			logger.info("Closing Connection Success");
			return true;
		} catch (Exception e) {
			logger.error("Closing Connection failed " + e.getMessage());
			return false;
		}
	}

	public boolean addResults(int userid, String test, float score) {
		String[] fields = TestFields.TESTS_FIELDS_2;
		String[] values = {String.valueOf(userid), test, String.valueOf(score), "sysdate"};

		String stmt = DatabaseHelper.createInsertStatement("tests", fields, values);


		return executeUpdate(stmt);
	}

	public int insertGroupTest(String groupId, String testId) {
		String[] fields = GroupTestLookupFields.GROUP_TEST_FIELDS;
		String[] values = {"NULL", groupId, testId};

		String stmt = DatabaseHelper.createInsertStatement("group_test_lookup", fields, values);

		return executeUpdate(stmt) == false ? -1 : 1;

	}

	public int insertGroupUser(String groupId, String userId) {
		String[] fields = GroupUserLookupFields.GROUP_USER_FIELDS;
		String[] values = {"NULL", userId, groupId};

		String stmt = DatabaseHelper.createInsertStatement("group_user_lookup", fields, values);

		return executeUpdate(stmt) == false ? -1 : 1;
	}

	public String getDataBaseInfo() {
		if (databaseInfo == null || databaseInfo.equals("")) {
			DatabaseMetaData dbmd;
			try {
				if (initialized == false)
					initialize();

				dbmd = con.getMetaData();
				databaseInfo = dbmd.getDatabaseProductName() + " " + dbmd.getDatabaseProductVersion();
			} catch (Exception e) {
				logger.error("Unable to get db info: " + e.getMessage());
			}					
		}

		return databaseInfo == null ? "" : databaseInfo;
	}

}
